import sys
import io
from urllib import request
from bs4 import BeautifulSoup
import MySQLdb
import time

# 打开数据库连接
db = MySQLdb.connect("localhost", "root", "lxyessc_2020", "essc_m_wx", charset='utf8' )

# 使用cursor()方法获取操作游标
cursor = db.cursor()

sys.stdout = io.TextIOWrapper(sys.stdout.buffer,encoding='utf8') #改变标准输出的默认编码

count = 0
for x in range(790001, 799999):
    count = count + 1
    if count == 20:
        # 按秒数来算
        time.sleep(1)
    #登录后才能访问的网站
    url = 'http://www.jdzj.com/shop/contact-' + str(x) + '.html'

    #浏览器登录后得到的cookie，也就是刚才复制的字符串
    cookie_str = r'__tins__1116951=%7B%22sid%22%3A%201637839989682%2C%20%22vd%22%3A%201%2C%20%22expires%22%3A%201637841789682%7D; __51cke__=; __51laig__=1; Hm_lvt_b4ecd77a89fe462248468d8286f067a8=1637839990; Hm_lpvt_b4ecd77a89fe462248468d8286f067a8=1637841048'

    # #登录后才能访问的网页
    # url = 'http://ssfw.xmu.edu.cn/cmstar/index.portal'

    req = request.Request(url)
    #设置cookie
    req.add_header('cookie', cookie_str)
    #设置请求头
    req.add_header('User-Agent', 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36')
    resp = None
    try:
        resp = request.urlopen(req)
    except:
        # 发生错误时回滚
        continue

    soup = BeautifulSoup(resp.read().decode('utf-8'))
    # print(soup)
    # print('ID:', x)
    row = {'id':x,'name':'','address':'','tel1':'','tel2':'','other':'','linkman':''}
    for tag in soup.find_all('h3'):
        row['name'] = tag.text

    for tag in soup.find_all('span'):
        strs = tag.text.split('：')
        if '地址：' in tag.text:
            if len(strs) == 2:
                row['address'] = strs[1]
            # print(row['address'])

    for tag in soup.find_all('li'):
        # print(tag.text)
        if '联系人' in tag.text:
            strs = tag.text.split('：')
            if len(strs) == 2:
                row['linkman'] = strs[1]
        elif '电话' in tag.text:
            strs = tag.text.split('：')
            if len(strs) == 2:
                row['tel2'] = strs[1]
        elif '手机' in tag.text:
            strs = tag.text.split('：')
            if len(strs) == 2:
                row['tel1'] = strs[1]
        elif '手机' in tag.text:
            strs = tag.text.split('：')
            if len(strs) == 2:
                row['tel1'] = strs[1]
    partition = ''
    if '苏州' in row['address'] or '苏州' in row['name']:
        partition = '苏州'
    elif '绍兴' in row['address'] or '绍兴' in row['name']:
        partition = '绍兴'
    elif '无锡' in row['address'] or '无锡' in row['name']:
        partition = '无锡'
    elif '常州' in row['address'] or '常州' in row['name']:
        partition = '常州'
    elif '宜兴' in row['address'] or '宜兴' in row['name']:
        partition = '宜兴'
    elif '江阴' in row['address'] or '江阴' in row['name']:
        partition = '江阴'
    elif '徐州' in row['address'] or '徐州' in row['name']:
        partition = '徐州'
    print(row)

    # SQL 插入语句
    sql = "INSERT INTO CRAWLDATA200(ID, \
           NAME,ADDRESS, TEL1, TEL2, MEMO, PART,LINKMAN) \
           VALUES (%s, '%s', '%s', '%s', '%s', '%s' , '%s' , '%s')" % \
          (row['id'],row['name'], row['address'], row['tel1'], row['tel2'], row['other'],partition,row['linkman'])
    try:
        # 执行sql语句
        # print(sql)
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        # 发生错误时回滚
        db.rollback()

# 关闭数据库连接
db.close()